﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Cmp;
using Sunny.UI;
using Sunny.UI.Win32;

namespace RclSystem
{
    public partial class StatementAccount : UIPage
    {
        List<StatementAccountVo> sortedVolist = new List<StatementAccountVo>();
        
        public StatementAccount()
        {
            InitializeComponent();
        }

        private void queryAccounts()
        {
            sortedVolist = new List<StatementAccountVo>();
            uiDataGridView1.Rows.Clear();
            List<StatementAccountVo> volist = new List<StatementAccountVo>();
            //获取发货单列表
            DataTable dt_deliver = MySQLHelper.ExecuteQuery("SELECT * FROM tbl_delivery where companyname like '%" + txt_company.Text.Trim() + "%' and deliverdate >= '" + dt_qstart.Value.ToString("yyyy-MM-dd") 
                + "' and deliverdate <= '" + dt_qend.Value.ToString("yyyy-MM-dd") + "' order by deliverdate");

            if(dt_deliver.Rows.Count > 0)
            {
                foreach (DataRow row in dt_deliver.Rows)
                {
                    StatementAccountVo vo = new StatementAccountVo();
                    vo.companyname = row["companyname"].ToString();
                    vo.optdate = (DateTime)row["deliverdate"];
                    vo.unitprice = double.Parse(row["perton"].ToString());
                    vo.count = double.Parse(row["salecount"].ToString());
                    vo.deliverprice = double.Parse(row["saleamount"].ToString());
                    volist.Add(vo);
                }
            }

            //获取收款单列表
            DataTable dt_receipt = MySQLHelper.ExecuteQuery("SELECT * FROM tbl_receipt where companyname like '%" + txt_company.Text.Trim() + "%' and receiptdate >= '" + dt_qstart.Value.ToString("yyyy-MM-dd")
                + "' and receiptdate <= '" + dt_qend.Value.ToString("yyyy-MM-dd") + "' order by receiptdate");

            //期初数结算金额
            double initaljs = 0;
            if (dt_receipt.Rows.Count > 0)
            {
                foreach (DataRow row in dt_receipt.Rows)
                {
                    StatementAccountVo vo = new StatementAccountVo();
                    vo.companyname = row["companyname"].ToString();
                    vo.optway = row["receiptway"].ToString();
                    vo.optdate = (DateTime)row["receiptdate"];
                    vo.receiptprice = double.Parse(row["receiptamount"].ToString());
                    if (row["parentid"].ToString() == "0")
                    {
                        initaljs += vo.receiptprice;
                    }
                    volist.Add(vo);
                }
            }

            //判断是否有期初数
            //获取单位信息
            DataTable dt_company = MySQLHelper.ExecuteQuery("SELECT * FROM tbl_company where name like '%" + txt_company.Text.Trim() + "%'");
            if (dt_company.Rows.Count == 1)
            {
                double initalamount = double.Parse(dt_company.Rows[0]["initalamount"].ToString());
                StatementAccountVo vo = new StatementAccountVo();
                vo.companyname = dt_company.Rows[0]["name"].ToString();
                vo.optway = "期初数";
                vo.optdate = DateTime.Parse("0001-01-01");
                vo.deliverprice = initalamount + initaljs;
                volist.Add(vo);
            }

            //对清单进行排序-按日期升序
            sortedVolist = volist.OrderBy(p => p.optdate).ToList();
            double tmprice = 0;//应收结存
            //显示
            foreach(StatementAccountVo vo in sortedVolist)
            {
                tmprice = tmprice + vo.deliverprice - vo.receiptprice;
                int index = this.uiDataGridView1.Rows.Add();
                this.uiDataGridView1.Rows[index].Cells[0].Value = index + 1;
                this.uiDataGridView1.Rows[index].Cells[1].Value = vo.companyname;
                this.uiDataGridView1.Rows[index].Cells[2].Value = vo.optdate.ToString("yyyy-MM-dd") == "0001-01-01" ? "" : vo.optdate.ToString("yyyy-MM-dd");
                this.uiDataGridView1.Rows[index].Cells[3].Value = vo.unitprice.ToString() == "0" ? "" : vo.unitprice.ToString();
                this.uiDataGridView1.Rows[index].Cells[4].Value = vo.count.ToString() == "0" ? "" : vo.count.ToString();
                this.uiDataGridView1.Rows[index].Cells[5].Value = vo.deliverprice.ToString() == "0" ? "" : vo.deliverprice.ToString();
                this.uiDataGridView1.Rows[index].Cells[6].Value = vo.optway;
                this.uiDataGridView1.Rows[index].Cells[7].Value = vo.receiptprice.ToString() == "0" ? "" : vo.receiptprice.ToString();
                this.uiDataGridView1.Rows[index].Cells[8].Value = vo.comment;
                this.uiDataGridView1.Rows[index].Cells[9].Value = Math.Round(tmprice,2);
            }
            
        }

        private void btn_query_Click(object sender, EventArgs e)
        {
            queryAccounts();
        }

        private void StatementAccount_Initialize(object sender, EventArgs e)
        {
            dt_qend.Value = DateTime.Now;
            dt_qstart.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
        }

        private void uiSymbolButton1_Click(object sender, EventArgs e)
        {
            ExportDataToExcel();
        }

        public void ExportDataToExcel()
        {
            string FileName = "对账单明细";
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            //设置文件标题
            saveFileDialog.Title = "导出Excel文件";
            //设置文件类型
            saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
            //设置默认文件类型显示顺序  
            saveFileDialog.FilterIndex = 1;
            //是否自动在文件名中添加扩展名
            saveFileDialog.AddExtension = true;
            //是否记忆上次打开的目录
            saveFileDialog.RestoreDirectory = true;
            //设置默认文件名
            saveFileDialog.FileName = FileName;
            //按下确定选择的按钮  
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                //获得文件路径 
                string localFilePath = saveFileDialog.FileName.ToString();

                //数据初始化
                int TotalCount;     //总行数

                TotalCount = sortedVolist.Count;

                //NPOI
                IWorkbook workbook;
                string FileExt = Path.GetExtension(localFilePath).ToLower();
                if (FileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (FileExt == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return;
                }
                ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);

                ICellStyle cellstyle = workbook.CreateCellStyle();
                cellstyle.VerticalAlignment = VerticalAlignment.Center;
                cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                sheet.SetColumnWidth(1, 30 * 256);
                sheet.SetColumnWidth(2, 12 * 256);
                sheet.SetColumnWidth(3, 0); //隐藏单价列
                sheet.SetColumnWidth(4, 12 * 256);
                sheet.SetColumnWidth(5, 12 * 256);
                sheet.SetColumnWidth(7, 12 * 256);
                sheet.SetColumnWidth(9, 12 * 256);

                try
                {
                    //读取标题  
                    int rowindex = 0;
                    IRow rowHeader = sheet.CreateRow(rowindex);
                    //添加标题
                    rowHeader.CreateCell(0).SetCellValue("序号");
                    rowHeader.CreateCell(1).SetCellValue("所属单位");
                    rowHeader.CreateCell(2).SetCellValue("日期");
                    rowHeader.CreateCell(3).SetCellValue("发货");
                    rowHeader.CreateCell(6).SetCellValue("结算");
                    rowHeader.CreateCell(9).SetCellValue("应收结存");

                    rowHeader.GetCell(1).CellStyle = cellstyle;

                    IRow rowHeader2 = sheet.CreateRow(++rowindex);
                    rowHeader2.CreateCell(3).SetCellValue("单价");
                    rowHeader2.CreateCell(4).SetCellValue("数量");
                    rowHeader2.CreateCell(5).SetCellValue("金额");
                    rowHeader2.CreateCell(6).SetCellValue("结算方式");
                    rowHeader2.CreateCell(7).SetCellValue("金额");
                    rowHeader2.CreateCell(8).SetCellValue("备注");

                    //合并标题
                    sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 1, 2, 2));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 5));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 1, 9, 9));
                    //读取数据  
                    int offset = 1;
                    double tmprice = 0;//应收结存
                    double count_total = 0; //数量合计
                    double deliver_total = 0;//发货金额合计
                    double receipt_total = 0;//收款金额合计
                    foreach (StatementAccountVo vo in sortedVolist)
                    {
                        tmprice = tmprice + vo.deliverprice - vo.receiptprice;
                        count_total += vo.count;
                        deliver_total += vo.deliverprice;
                        receipt_total += vo.receiptprice;

                        IRow rowData = sheet.CreateRow(++rowindex);
                        ICell cell = rowData.CreateCell(0);
                        cell.SetCellValue(offset++);

                        cell = rowData.CreateCell(1);
                        cell.SetCellValue(vo.companyname);

                        cell = rowData.CreateCell(2);
                        cell.SetCellValue(vo.optdate.ToString("yyyy-MM-dd"));

                        cell = rowData.CreateCell(3);
                        cell.SetCellValue(vo.unitprice);

                        cell = rowData.CreateCell(4);
                        cell.SetCellValue(vo.count);

                        cell = rowData.CreateCell(5);
                        cell.SetCellValue(vo.deliverprice);

                        cell = rowData.CreateCell(6);
                        cell.SetCellValue(vo.optway);

                        cell = rowData.CreateCell(7);
                        cell.SetCellValue(vo.receiptprice);

                        cell = rowData.CreateCell(8);
                        cell.SetCellValue(vo.comment);

                        cell = rowData.CreateCell(9);
                        cell.SetCellValue(Math.Round(tmprice, 2));
                    }
                    //合计行
                    IRow hjrow = sheet.CreateRow(++rowindex);
                    ICell hcell = hjrow.CreateCell(0);
                    hcell.SetCellValue(offset++);

                    hcell = hjrow.CreateCell(2);
                    hcell.SetCellValue("本期合计");

                    hcell = hjrow.CreateCell(4);
                    hcell.SetCellValue(Math.Round(count_total,3));

                    hcell = hjrow.CreateCell(5);
                    hcell.SetCellValue(Math.Round(deliver_total,2));

                    hcell = hjrow.CreateCell(7);
                    hcell.SetCellValue(Math.Round(receipt_total,2));

                    hcell = hjrow.CreateCell(9);
                    hcell.SetCellValue(Math.Round(tmprice, 2));

                    //合并所属单位列
                    sheet.AddMergedRegion(new CellRangeAddress(2, rowindex, 1, 1));

                    //转为字节数组  
                    MemoryStream stream = new MemoryStream();
                    workbook.Write(stream);
                    var buf = stream.ToArray();

                    //保存为Excel文件  
                    using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
                    {
                        fs.Write(buf, 0, buf.Length);
                        fs.Flush();
                        fs.Close();
                    }
                    
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }                
            }
        }
    }
}
